Pivot Tables
Executing the Create/Alter scripts from the ODS Utility will generate and insert a replicated database schema of the OIPA source in the target database (but not the table data).
- The schema of the ODS will be based on the configured screen and transaction rules where the dynamic field data will be pivoted into columns to create a flat data structure.
- All AsX...Field tables will be pivoted to columns for their respective entities and the columns being identified by the rules that define the fields.
- All AsX... Entity tables will become DsX... Entity tables. "Ds" will be the prefix for all ODS tables.
- There is a 27 character limit for the table name due to the need to create views as part of this process. These views will be the final DS table name with "_VW" appended on the end.
Most tables fall within this limit but a handful of tables will need to have abbreviated names. Those tables are:
- ASACCOUNTINGDETAILDISBURSEMENT
- ASALLOCATIONSETASSETCLASSFUND
- ASCLIENTRELATIONSHIPACTIVITY
- ASPLANDEFAULTALLOCATIONMODEL
- ASUNMATCHEDREQUIREMENTRESULT
Table Naming Conventions
ODS tables will be named for their respective entities, but few tables will take their names from user defined rule definitions (e.g. Product/Plan and Activity table names can be given custom names using the Alias Creation feature in the ODS utility).
Since Product/Plan and Activity tables in the ODS will be named for configured entities, it is possible for the user defined names to exceed the number of characters permitted (including the _VW suffix). In these cases, in addition to the automatic removal of spaces, and non-alphanumeric characters certain strings will be replaced with abbreviated terms in order to reduce the character count.
Once the ODS tables have been created, the Scheduler will begin migrating data from the source database to the ODS on the next update set time
Pivoting Example
A brief explanation on how the schema will transform from rows to columns in the field tables.
AsActivityField
| ACTIVITYGUID | FIELDNAME | FIELDTYPECODE | DATEVALUE | TEXTVALUE | INTVALUE | FLOATVALUE | OPTIONTEXTFLAG | OPTIONTEXT | CURRENCYCODE | BIGTEXTVALUE |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | PreTEFRAAmount | 04 | (null) | (null) | (null) | 0 | (null) | (null) | USD | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | WaivePremiumTax | 02 | (null) | 00 | (null) | (null) | 1 | No | (null) | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | TotalCostBasis | 04 | (null) | (null) | (null) | 0 | (null) | (null) | USD | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | UnknownCostBasis | 04 | (null) | (null) | (null) | 0 | (null) | (null) | USD | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | ConfirmationNumber | 02 | (null) | 0000011815 | (null) | (null) | (null) | (null) | (null) | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | PreTEFRAGain | 04 | (null) | (null) | (null) | 0 | (null) | (null) | USD | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | PaymentSource | 02 | (null) | 03 | (null) | (null) | 1 | Internal TOA | (null) | (null) |
| 1AAB6C6D-0830-4650-A92C-738D6A38B5CD | GrossAmount | 04 | (null) | (null) | (null) | 120000 | (null) | (null) | USD | (null) |
Becomes…
DSPREMIUM - Field names become columns and the table is only for transaction named ‘Premium’
| ACTIVITYGUID | 1AAB6C6D-0830-4650-A92C-738D6A38B5CD |
| PreTEFRAAmount | 0 |
| PreTEFRAAmountCurrency | USD |
| WaivePremiumTax | 00 |
| WaivePremiumTaxOptionText | No |
| TotalCostBasis | 0 |
| TotalCostBasisCurrency | USD |
| UnknownCostBasis | 0 |
| UnknownCostBasisCurrency | USD |
| ConfirmationNumber | 0000011815 |
| PreTEFRAGain | 0 |
| PreTEFRAGainCurrency | USD |
| PaymentSource | 03 |
| PaymentSourceOptionText | Internal TOA |
| GrossAmount | 120000 |
| GrossAmountCurrency | USD |
Note: For easy readability, the columns of the table are given here in the vertical presentation format.
Renaming ODS Tables
ODS tables can be renamed for their respective entities, but few tables will take their names from user defined rule definitions (e.g. Product/Plan and Activity table names can be given custom names using the Alias Creation feature in the ODS utility).
